
package com.zoo.transfer

import com.mongodb.BasicDBObject

class Customer extends BasicDBObject {

    static final String COLLECTION_NAME = "customer"

    static final String GET_CUSTOMER =
        """SELECT customer_id,type,email,password,reseller_code,shippingAccount,accwareAccount,notice,
        driversNumber,driversState,checkNumber,accountNumber,routingNumber,
        shippingCompany,shippingFirstName,shippingLastName,shipping_address1,shipping_address2,
        shipping_city,shipping_state,shipping_country,shipping_zip,shipping_zip4,shippingPhone,shippingCell,shippingFax,
        billingCompany,billingFirstName,billingLastName,billing_address1,billing_address2,
        billing_city,billing_state,billing_country,billing_zip,billing_zip4,billingPhone,billingCell,billingFax,
        cc_name,cardNumber,cc_number_add,cc_exp_date,bankPhone,joined
        FROM sklad.customer
where customer_id=47
        ORDER BY customer_id LIMIT ?,?"""

    static final String GET_ORDERS =
        """SELECT order_id,orderNo,order_date,customerPO,packingSlip slip,tax_price,
        paymentMethod,IF(warranty='Y',1,0) wty,amount,advert_id,advertRefID,auth_status,
        shipping_price,shipping_code,IF(shipping_sign=1,true,false) sign,IF(blindShipping=1,true,false) blind,
        IF(cod='Y',true,false) cod,trans_id,trans_fee,cjpid,cjaid,cjsid,IF(import='Y',true,false) imp
        FROM sklad.orders
        WHERE customer_id=?
        ORDER BY order_id """

    static final String GET_ORDER_LINE =
        """SELECT quantity,sell_price,cost_price,warrantyDesc,warrantyPrice,alias
        FROM sklad.order_line
        LEFT JOIN sklad.itemVendor USING(vendor_id)
        WHERE order_id=?"""

    static final String GET_CUSTOMER_JSON =
        """SELECT customer_id,email,IFNULL(CAST(
        CONCAT(
        '{cid:',customer_id,
        ',who:',QUOTE(type),
        ',eml:',QUOTE(REPLACE(email,'"','')),
        ',pwd:',QUOTE(password),
        IF(driversNumber='' OR checkNumber='' OR accountNumber='' OR routingNumber='','',
          CONCAT(',check:{',
            IF(driversNumber='','',CONCAT('licen:"',REPLACE(driversNumber,'"',''),'",')),
            IF(checkNumber='','',CONCAT('numb:"',checkNumber,'",')),
            IF(accountNumber='','',CONCAT('accnt:"',REPLACE(accountNumber,'"',''),'",')),
            IF(routingNumber='','',CONCAT('route:"',REPLACE(routingNumber,'"',''),'"')),
        '}')),
        ',cards:[{',
          IF(cc_name='','',CONCAT('name:"',cc_name,'",')),
          IF(ENCODE(cardNumber,'crypt')=0,'',CONCAT('numb:',ENCODE(cardNumber,'crypt'),',')),
          IF(cc_number_add='' OR cc_number_add=0,'',CONCAT('add:',cc_number_add,',')),
          IF(cc_exp_date='','',CONCAT('exp:"',cc_exp_date,'",')),
          IF(bankPhone='','',CONCAT('ph:"',REPLACE(bankPhone,'"',''),'"')),
        '}]',
        ',post:[{',
          'tp:"B",',
          IF(shippingCompany='','',CONCAT('cmp:"',REPLACE(shippingCompany,'"',''),'",')),
          IF(shippingFirstName='','',CONCAT('fn:"',REPLACE(shippingFirstName,'"',''),'",')),
          IF(shippingLastName='','',CONCAT('ln:"',REPLACE(shippingLastName,'"',''),'",')),
          IF(shipping_address1='','',CONCAT('ad1:"',REPLACE(shipping_address1,'"',''),'",')),
          IF(shipping_address2='','',CONCAT('ad2:"',REPLACE(shipping_address2,'"',''),'",')),
          IF(shipping_city='','',CONCAT('cty:"',REPLACE(shipping_city,'"',''),'",')),
          IF(shipping_state='','',CONCAT('stt:"',REPLACE(shipping_state,'"',''),'",')),
          IF(shipping_country='','',CONCAT('cnty:"',REPLACE(shipping_country,'"',''),'",')),
          IF(shipping_zip='','',CONCAT('zp:',QUOTE(shipping_zip),',')),
          IF(shipping_zip4='','',CONCAT('zp4:',QUOTE(shipping_zip4),',')),
          IF(shippingPhone='','',CONCAT('ph:"',REPLACE(shippingPhone,'"',''),'",')),
          IF(shippingCell='','',CONCAT('cel:"',REPLACE(shippingCell,'"',''),'",')),
          IF(shippingFax='','',CONCAT('fx:"',REPLACE(shippingFax,'"',''),'"')),
        '},{',
          'tp:"S",',
          IF(billingCompany='','',CONCAT('cmp:"',REPLACE(billingCompany,'"',''),'",')),
          IF(billingFirstName='','',CONCAT('fn:"',REPLACE(billingFirstName,'"',''),'",')),
          IF(billingLastName='','',CONCAT('ln:"',REPLACE(billingLastName,'"',''),'",')),
          IF(billing_address1='','',CONCAT('ad1:"',REPLACE(billing_address1,'"',''),'",')),
          IF(billing_address2='','',CONCAT('ad2:"',REPLACE(billing_address2,'"',''),'",')),
          IF(billing_city='','',CONCAT('cty:"',REPLACE(billing_city,'"',''),'",')),
          IF(billing_state='','',CONCAT('stt:"',REPLACE(billing_state,'"',''),'",')),
          IF(billing_country='','',CONCAT('cnty:"',REPLACE(billing_country,'"',''),'",')),
          IF(billing_zip='','',CONCAT('zp:',QUOTE(billing_zip),',')),
          IF(billing_zip4='','',CONCAT('zp4:',QUOTE(billing_zip4),',')),
          IF(billingPhone='','',CONCAT('ph:"',REPLACE(billingPhone,'"',''),'",')),
          IF(billingCell='','',CONCAT('cel:"',REPLACE(billingCell,'"',''),'",')),
          IF(billingFax='','',CONCAT('fx:"',REPLACE(billingFax,'"',''),'"')),
        '}]',
        ',ords:[]',
        ',join:',CONCAT('{"\$date":',DATE_FORMAT(joined,'"%Y-%m-%dT%T.000Z"'),'}'),
        '}') AS CHAR),'{}') json
        FROM sklad.customer
        ORDER BY customer_id LIMIT ?,?"""
//where customer_id=47

    static final String GET_ORDERS_JSON =
        """SELECT order_id,IFNULL(CAST(
        CONCAT(
        '{oid:',order_id,
        ',no:',QUOTE(orderNo),
        IF(customerPO='','',CONCAT(',po:"',REPLACE(customerPO,'"',''),'"')),
        IF(packingSlip=0,'',',slip:1'),
        IF(paymentMethod='','',CONCAT(',pay:"',paymentMethod,'"')),
        IF(tax_price=0,'',CONCAT(',tax:',tax_price)),
        ',amnt:',amount,
        IF(warranty='N','',',wty:1'),
        IF(auth_status='','',CONCAT(',auth:"',REPLACE(auth_status,'"',''),'"')),
        ',ship:{',
          CONCAT('prc:',shipping_price,','),
          IF(shipping_code='','',CONCAT('code:"',REPLACE(shipping_code,'"',''),'",')),
          IF(shipping_sign=0,'','sing:1,'),
          IF(blindShipping=0,'','blnd:1,'),
          IF(cod='N','','cod:1'),
        '}',
        ',advr:{',
          CONCAT('id:"',sd.name,'"'),
          IF(advertRefID='','',CONCAT(',ref:"',REPLACE(advertRefID,'"',''),'"')),
        '}',
        IF(trans_id IS NULL AND trans_fee=0,'',
          CONCAT(',trans:{',
          IF(IFNULL(trans_id,'')='','',CONCAT('id:"',REPLACE(trans_id,'"',''),'",')),
          IF(trans_fee=0,'',CONCAT('fee:',trans_fee)),
        '}')),
        IF(cjpid IS NULL AND cjaid IS NULL AND (cjsid IS NULL OR cjsid=''),'',
          CONCAT(',cj:{',
          IF(IFNULL(cjpid,'')='','',CONCAT('pid:',cjpid,',')),
          IF(IFNULL(cjaid,'')='','',CONCAT('aid:',cjaid,',')),
          IF(IFNULL(cjsid,'')='','',CONCAT('sid:"',REPLACE(cjsid,'"',''),'"')),
        '}')),
        ',imp:',IF(import='Y',1,0),
        ',itms:[]',
        ',date:',CONCAT('{"\$date":',DATE_FORMAT(order_date,'"%Y-%m-%dT%T.000Z"'),'}'),
        '}') AS CHAR),'{}') json
        FROM sklad.orders
        LEFT JOIN sklad.advert sd USING(advert_id)
        WHERE customer_id=?
        ORDER BY order_id"""

    static final String GET_ORDER_LINE_JSON =
        """SELECT IFNULL(CAST(
        CONCAT(
         '{iid:',item_id,
         ',qty:',quantity,
         ',sell:',sell_price,
         ',cost:',cost_price,
         IF(warrantyDesc='' AND warrantyPrice=0,'',
           CONCAT(',wty:{',
           IF(warrantyDesc='','',CONCAT('desc:"',warrantyDesc,'",')),
           IF(warrantyPrice=0,'',CONCAT('prc:',warrantyPrice)),
         '}')),
         ',vnd:',QUOTE(alias),
         '}') AS CHAR),'{}') json
        FROM sklad.order_line
        LEFT JOIN sklad.itemVendor USING(vendor_id)
        WHERE order_id=?
        ORDER BY order_id"""














//db.customer.update({id:44, 'order.id':244321},{$set: {'ords.$.no':6 } }) // update some filds
//db.customer.update({id:47},{$push: {'ords:{imp:1} } }) // insert
//db.customer.update({id:47},{$inc: {cnt:1 } }) // insert

//db.customer.update({id:47,'ords.no':'200511121861'},{$set: {'ords.$': [{id:1},{id:2}] } }) // first time add items

//db.customer.update({id:47,'ords.no':'200511121861'},{$push: {'ords.$.items': {item_is:1} } }) // add new items to aray
//db.customer.update({'ords.no':'200511121861'},{$push: {'ords.$.itms': {id:1} } }) // insert new items

}

